/*NOTE: This is model code to illustrate the logic used to generate the datasets for analysis in the SCARP pilot.
This code is not intended to run as is - due to Epic restrictions on the sharing of intellectual property all 
of the table and attribute names have been "masked" with names enclosed in square brackets if they were pulled 
from Clarity (EPIC).*/

/*****************************************************
PROJECT:  BRIDGE C2 
PILOT: SCRAP
DATE: 7/22/2022

DESCRIPTION: 
Use ASCEND approach to compare CRC, CVC and 
BC cancer prevention services in patients with
	No SDH Screening
	Documented Need
	Documented No Need
Stratified by SDH need: Food, Housing, Transportation
STUDY PERIOD: 7/1/2016-2/29/2020		

Note: SDH screens for this project were pulled and sent to OHSU for ASCEND. 
This project uses that pull for the SDH variables as the study date range has not changed.

DETAILS (This Syntax): 
Pull raw_patids for patients in ASCEND data pull, link to scrap encounters file and patient file

INPUT DATA: 
ASCEND.AIM1_8_SDH_screening_2020: SDH screening data pulled for prior analysis 
ASCEND.AIM1_8_ENCOUNTER_2020: Patient encounter data pulled for prior analysis
ASCEND.AIM1_8_PATIENT_2020: Patient demographic data pulled for prior analysis
OCHIN.demographic
OCHIN.ENCOUNTER


OUTPUT DATA:
SCRAP.SCRAP_ASCEND_patid_xwalk
SCRAP.SCRAP_encounters
SCRAP.SCRAP_SDH_screening

SECTIONS:
A. Link ASCEND encounters to sex_r,
	Get raw_patids for all potential SCRAP patients in ASCEND encounters
		Males 50-74
		Females 23-74 

B. link raw_patid and ADVANCE_patid to ASCEND SDH_screening, Encounters
		>>SCRAP.SCRAP_encounters
		>>SCRAP.SCRAP_SDH_screening

*****************************************************/
options compress=yes reuse=yes;
libname SCRAP "e:\\sasroot\BRIDGE C2\SCRAP\";

/*A. Link ASCEND encounters to sex_r,*/

/*	Get raw_patids for all potential SCRAP patients in ASCEND encounters*/
/* SCRAP patients are patients who meet the UDS criteria for recommended
   screening for CRC, CVC or BC*/
/*		Males 50-74*/
/*		Females 23-74 */

proc sql;
create table encounter as
select 
e.*,
p.sex_r
from ASCEND.AIM1_8_ENCOUNTER_2020 e
left join ASCEND.aim1_8_patient_2020 p
on e.patid=p.patid;

/*Limit to potential scrap encounters*/
data encounter;
set encounter;
rename patid=ASCEND_patid;
/* age range for CRC screening, contains age range for BC screening*/
if age_at_encounter_date>=50 then do;
	if age_at_encounter_date<75 then output;
end;
/*add women too young for CRC screening but in age range for CVC screening*/
else if age_at_encounter_date<50 then do;
	if sex_r='F' then do;
		if age_at_encounter_date>=23 then output;
	end;
end;
run;


/*
B. Determine current ADVANCE_patids
C. Create crosswalk of raw_PATid, ADVANCE_patid to ASCEND_patid

These two sections are internal to create a crosswalk of raw_PATid, ADVANCE_patid to ASCEND_patid: 
	output file:SCRAP.SCRAP_ASCEND_patid_xwalk
*/


/*D. link raw_patid and ADVANCE_patid to ASCEND SDH_screening, Encounters*/
/*		>>SCRAP.SCRAP_encounters*/
/*		>>SCRAP.SCRAP_SDH_screening*/

proc sql;
create table SCRAP.SCRAP_encounters as
select x.ADVANCE_patid,
x.raw_patid,
e.*
from SCRAP.SCRAP_ASCEND_patid_xwalk x left join encounter e
on x.ascend_patid=e.ascend_patid;


proc sql;
create table SCRAP.SCRAP_SDH_screening as
select x.ADVANCE_patid,
x.raw_patid,
e.*
from SCRAP.SCRAP_ASCEND_patid_xwalk x left join ascend.aim1_8_sdh_screening_2020 e
on x.ascend_patid=e.patid;

